package com.hetang.dao;

import com.hetang.common.StringUtils;
import com.hetang.domain.User;
import com.hetang.domain.UserRequestDTO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 詹泽
 * @date 2017/3/3
 */
@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询
     * @param
     * @return
     */
    public User getUser(int id){
        String sql = "SELECT id,code,name,notes,status,user_type,gmt_create,gmt_last_signin FROM user WHERE id = ?";
        User user = jdbcTemplate.queryForObject(sql,new Object[]{id},userMapper());
        return user;
    }

    public Integer getTotalRecodes(UserRequestDTO userRequestDTO){
        StringBuilder sql = new StringBuilder("select count(*) from user where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getCode())){
            sql.append(" AND code LIKE ?");
            paramsList.add("%"+userRequestDTO.getCode()+"%");
        }
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getName())){
            sql.append(" AND name LIKE ?");
            paramsList.add("%"+userRequestDTO.getName()+"%");
        }
        if(userRequestDTO.getStartTime() != null){
            sql.append(" AND gmt_create BETWEEN ? AND ?");
            paramsList.add(userRequestDTO.getStartTime());
            paramsList.add(userRequestDTO.getEndTime());
        }
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getUserType())){
            sql.append(" AND user_type = ?");
            paramsList.add(userRequestDTO.getUserType());
        }
        Object[] objs = paramsList.toArray();
        Integer count = jdbcTemplate.queryForObject(sql.toString(),objs,Integer.class);
        return count;
    };

    public boolean updateUserStatus(String id,int status){
        String sql = "update user set status = ? where id = ?";
        Object[] objs = new Object[]{status,id};
        int rs = jdbcTemplate.update(sql,objs);
        if(rs > 0){
            return true;
        }
        return false;
    }

    public boolean deleteUser(String id){
        StringBuilder sb = new StringBuilder("DELETE FROM USER WHERE id = ?");
        int rs = jdbcTemplate.update(sb.toString(),id);
        if(rs > 0){
            return true;
        }
        return false;
    }
    /**
     * 修改
     */
    public boolean updateUser(User user){
        StringBuilder sb = new StringBuilder("UPDATE USER SET ");
        List<String> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        if(!StringUtils.isNullOrEmpty(user.getCode())){
            list.add("code = ?");
            params.add(user.getCode());
        }
        if(!StringUtils.isNullOrEmpty(user.getName())){
            list.add("name = ?");
            params.add(user.getName());
        }
        if(!StringUtils.isNullOrEmpty(user.getNotes())){
            list.add("notes = ?");
            params.add(user.getNotes());
        }
        if(!(user.getStatus() == null)){
            list.add("status = ?");
            params.add(user.getStatus());
        }
        if(!StringUtils.isNullOrEmpty(user.getUserType())){
            list.add("user_type = ?");
            params.add(user.getUserType());
        }
        if(list.size() == 0){
            return false;
        }
        System.out.println(StringUtils.listToString(list));
        sb.append(StringUtils.listToString(list));
        sb.append(" where id = ?");
        params.add(user.getId());
        int rs = jdbcTemplate.update(sb.toString(),params.toArray());
        if(rs > 0){
            return true;
        }
        return false;
    }
    /**
     * 增加
     * @param user
     * @return
     */
    public boolean addUser(User user){
        String sql = "INSERT INTO user(code,name,notes,status,user_type,gmt_create,gmt_last_signin)\n" +
                "VALUES(?,?,?,?,?,?,?)";
        Object[] objs = new Object[]{user.getCode(),user.getName(),user.getNotes(),
                user.getStatus(),user.getUserType(),user.getGmtCreate(),user.getGmtLastSignin()};
        int rs = jdbcTemplate.update(sql,objs);
        if(rs > 0){
            return true;
        }
        return false;
    };
    /**
     * 查询
     * @param
     * @return
     */
    public List<User> listUser(UserRequestDTO userRequestDTO){
        StringBuilder sql = new StringBuilder("SELECT id,code,name,notes,status,user_type,gmt_create,gmt_last_signin FROM user WHERE 1=1");
        List<Object> paramsList = new ArrayList<>();
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getCode())){
            sql.append(" AND code LIKE ?");
            paramsList.add("%"+userRequestDTO.getCode()+"%");
        }
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getName())){
            sql.append(" AND name LIKE ?");
            paramsList.add("%"+userRequestDTO.getName()+"%");
        }
        if(userRequestDTO.getStartTime() != null){
            sql.append(" AND gmt_create BETWEEN ? AND ?");
            paramsList.add(userRequestDTO.getStartTime());
            paramsList.add(userRequestDTO.getEndTime());
        }
        if(!StringUtils.isNullOrEmpty(userRequestDTO.getUserType())){
            sql.append(" AND user_type = ?");
            paramsList.add(userRequestDTO.getUserType());
        }
        Object[] objs = paramsList.toArray();
        List<User> list = jdbcTemplate.query(sql.toString(),objs,userMapper());
        return list;
    }

    /**
     * entity映射
     * @return
     */
    private RowMapper<User> userMapper(){
        return new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int i) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setCode(rs.getString("code"));
                user.setNotes(rs.getString("notes"));
                user.setStatus(rs.getInt("status"));
                user.setName(rs.getString("name"));
                user.setUserType(rs.getString("user_type"));
                user.setGmtCreate(rs.getTimestamp("gmt_create"));
                user.setGmtLastSignin(rs.getTimestamp("gmt_last_signin"));
                return user;
            }
        };
    }
}
